﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Configuration;
using BulldogCMS.Core;
using System.Xml.Linq;

namespace BulldogCMS.SQLCompact
{
    public class SQLCompactContentTreeProvider : BulldogCMS.Core.Storage.Providers.BaseContentTreeProvider, BulldogCMS.Core.Storage.Providers.IContentTreeProvider
    {
        private string ConnectionStringName
        {
            get;
            set;
        }

        private string _connectionString = null;

        private string ConnectionString
        {
            get
            {
                if (this._connectionString == null)
                {
                    this._connectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName].ConnectionString;
                }
                return this._connectionString;
            }
        }

        public IEnumerable<Core.Storage.Node> GetRootNodes(long? nodeID = null)
        {
            using (SqlCeConnection conn = new SqlCeConnection(this.ConnectionString))
            {
                try
                {
                    var command = conn.CreateCommand();
                    if (nodeID == null)
                    {
                        command.CommandText = "SELECT * FROM Node WHERE ParentNodeID IS NULL";
                    }
                    else
                    {
                        command.CommandText = "SELECT * FROM Node WHERE ParentNodeID = @NodeID";
                        command.Parameters.AddWithValue("@NodeID", nodeID);
                    }
                    conn.Open();
                    var reader = command.ExecuteReader();
                    List<Core.Storage.Node> list = new List<Core.Storage.Node>();
                    while (reader.Read())
                    {
                        Core.Storage.Node node = Core.Storage.Node.CreateNode(
                            ID: (long)reader["NodeID"],
                            createdOn: (DateTime)reader["CreatedOn"],
                            mode: (NodeMode)reader["Mode"],
                            templateName: (string)reader["TemplateName"],
                            name: (string)reader["Name"],
                            parentNodeID: reader["ParentNodeID"] as long?,
                            languageLock: reader["LanguageLock"] as string
                        );
                        list.Add(node);
                    }
                    return list;
                }
                finally
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

        public IEnumerable<Core.Storage.Node> GetNodes(Core.Storage.Node parentNode)
        {
            using (SqlCeConnection conn = new SqlCeConnection(this.ConnectionString))
            {
                try
                {
                    var command = conn.CreateCommand();
                    command.CommandText = "SELECT * FROM Node WHERE ParentNodeID = @ParentNodeID";
                    command.Parameters.AddWithValue("@ParentNodeID", parentNode.ID);

                    conn.Open();
                    var reader = command.ExecuteReader();
                    List<Core.Storage.Node> list = new List<Core.Storage.Node>();
                    while (reader.Read())
                    {
                        Core.Storage.Node node = Core.Storage.Node.CreateNode(
                            ID: (long)reader["NodeID"],
                            createdOn: (DateTime)reader["CreatedOn"],
                            mode: (NodeMode)reader["Mode"],
                            templateName: (string)reader["TemplateName"],
                            name: (string)reader["Name"],
                            parentNodeID: reader["ParentNodeID"] as long?,
                            languageLock: reader["LanguageLock"] as string
                        );
                        list.Add(node);
                    }
                    return list;
                }
                finally
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

        public Core.Storage.Node GetNode(string currentProviderPath, string name, long parentNodeID)
        {
            using (SqlCeConnection conn = new SqlCeConnection(this.ConnectionString))
            {
                try
                {
                    var command = conn.CreateCommand();
                    command.CommandText = "SELECT * FROM Node WHERE ParentNodeID = @ParentNodeID AND Name = @Name";
                    command.Parameters.AddWithValue("@ParentNodeID", parentNodeID);
                    command.Parameters.AddWithValue("@Name", name);
                    conn.Open();
                    var reader = command.ExecuteReader();
                    List<Core.Storage.Node> list = new List<Core.Storage.Node>();
                    if (reader.Read())
                    {
                        Core.Storage.Node node = Core.Storage.Node.CreateNode(
                            ID: (long)reader["NodeID"],
                            createdOn: (DateTime)reader["CreatedOn"],
                            mode: (NodeMode)reader["Mode"],
                            templateName: (string)reader["TemplateName"],
                            name: (string)reader["Name"],
                            parentNodeID: reader["ParentNodeID"] as long?,
                            languageLock: reader["LanguageLock"] as string
                        );
                        return node;
                    }
                    else
                    {
                        return null;
                    }
                }
                finally
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

        public Core.Storage.NodeVersion GetNodeVersion(Core.Storage.Node node, string languageIdent, int versionNumber)
        {
            using (SqlCeConnection conn = new SqlCeConnection(this.ConnectionString))
            {
                try
                {
                    var command = conn.CreateCommand();
                    command.CommandText = "SELECT * FROM Version WHERE NodeID = @NodeID AND Number = @Number AND Language = @Language";
                    command.Parameters.AddWithValue("@NodeID", node.ID);
                    command.Parameters.AddWithValue("@Number", versionNumber);
                    command.Parameters.AddWithValue("@Language", languageIdent);

                    conn.Open();
                    var reader = command.ExecuteReader();
                    List<Core.Storage.Node> list = new List<Core.Storage.Node>();
                    if (reader.Read())
                    {
                        string language =   reader["Language"] as string;
                        int verNumber =     (int)reader["Number"];
                        string data =       reader["Data"] as string;

                        Core.Storage.NodeVersion version = Core.Storage.NodeVersion.CreateNodeVersion(verNumber, language);

                        if (data != null)
                        {
                            XDocument xDoc = XDocument.Parse(data);
                            this.FillVersionComplexData(version, xDoc);
                        }

                        return version;
                    }
                    else
                    {
                        return null;
                    }
                }
                finally
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }            
        }

        #region Editing Region

        public Core.Storage.Node CreateNewNode(Core.Storage.Node node)
        {
            throw new NotImplementedException();
        }

        public Core.Storage.Node UpdateNode(Core.Storage.Node node)
        {
            throw new NotImplementedException();
        }

        public void DeleteNode(Core.Storage.Node node)
        {
            throw new NotImplementedException();
        }

        public Core.Storage.NodeVersion CreateNewVersion(Core.Storage.Node node, Core.Storage.NodeVersion newVersion)
        {
            throw new NotImplementedException();
        }

        public Core.Storage.NodeVersion UpdateVersion(Core.Storage.Node node, Core.Storage.NodeVersion updatedVersion)
        {
            throw new NotImplementedException();
        }

        public Core.Storage.NodeVersion DeleteVersion(Core.Storage.Node node, Core.Storage.NodeVersion versionToBeDeleted)
        {
            throw new NotImplementedException();
        }

        #endregion

        public bool ReadOnly
        {
            get { return false; }
        }

        public void SetParameter(string name, string value)
        {
            if ("ConnectionStringName".Equals(name, StringComparison.InvariantCultureIgnoreCase))
            {
                this.ConnectionStringName = value;
            }
        }

    }
}
